 /**
 * 逻辑、功能相关描述: <br>
 *
 * @author 付湘辉  <br>
 * 编写日期：2008-12-15  <br>
 */
package com.ce.pms.custservice.dao.impl;


import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Query;
import com.ce.common.util.BlankUtil;
import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.common.util.SqlConfig;
import com.ce.pms.common.util.StringAnalysiUtil;
import com.ce.pms.custservice.dao.IDispatchFormDao;
import com.ce.pms.custservice.model.AbstractServiceItem;
import com.ce.pms.custservice.model.DispatchFormDO;
import com.ce.pms.custservice.model.FeedbackRecord;
import com.ce.pms.custservice.vo.DispatchFormSearchVO;
import com.ce.pms.custservice.vo.FeedbackSearchVO;
import com.ce.pms.custservice.vo.ServItemSearchVO;
import com.sitechasia.webx.core.support.Page;

/**
 * 	名称: com.ce.pms.custservice.dao.impl.DispatchFormDaoImpl <br>
 *
 *
 * @author  付湘辉  <br>
 * Created on： 2008-12-15 <br>
 * @author <br>
 * update on： <br>
 * why: <br>
 */
public class DispatchFormDaoImpl extends PmsBaseDaoImpl<DispatchFormDO> implements
		IDispatchFormDao {

	private static final String CUST_EVENT_DISPATCHFORM_SQL = "CustEvent_DispatchFormSQL.xml";

	private static final String KEY_QUERY_DISPATCHFORM_BYDEPT = "queryDispatchFormByDept";

	private static final String KEY_QUERY_DISPATCHFORM_BYEVENT = "queryDispatchFormByEvent";

	/**
	 * 功能描述: 根据服务费用项目id查询一个服务费用项目。 <br>
	 * @param serviceItemId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-12-16  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public AbstractServiceItem getAbstractServiceItemById(Long serviceItemId) {
		String hql = "from AbstractServiceItem item where item.id=? ";
		Query query = super.createQuery(hql, serviceItemId);
		Object obj = query.uniqueResult();
		AbstractServiceItem serviceItem = obj==null?null:(AbstractServiceItem)obj;
		return serviceItem;
	}

	/**
	 * 功能描述: 根据反馈id查询一个派工单反馈。 <br>
	 * @param feedbackRecordId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-12-16  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public FeedbackRecord getFeedbackRecordById(Long feedbackRecordId) {
		String hql = "from FeedbackRecord fbr where fbr.id=? ";
		Query query = super.createQuery(hql, feedbackRecordId);
		Object obj = query.uniqueResult();
		FeedbackRecord feedbackRecord = obj==null?null:(FeedbackRecord)obj;
		return feedbackRecord;
	}

	/* *
	 * 功能描述: 根据派工单id查询派工单反馈。 <br>
	 * @param feedbackRecordId
	 * @return
	 *
	 * Create author:高嵩 <br>
	 * Create on:2008-12-19  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	 /*public Page queryFeedbackRecordByPage(DispatchFormSearchVO searchVO){

		 Map<String,Object> paramMap = new HashMap<String,Object>();
  		StringBuffer strBuf = new StringBuffer("from FeedbackRecord fbr where 1=1 ");

			if(null!=searchVO.getDispatchNo()){

				strBuf.append(" and fbr.dispatchForm.keyword = :keyword");
				paramMap.put("keyword", searchVO.getDispatchNo());
			}

			 if(null!=searchVO.getSortStr()&& !"".equals(searchVO.getSortStr())){

				strBuf.append(" order by ").append(searchVO.getSortStr()).append(" desc");
			}else{
				strBuf.append(" order by fbr.dispatchForm.dispatchNo desc");
			}
			return super.pagedNameQueryByPageNo(strBuf.toString(),
					searchVO.getPageNo(),
					searchVO.getPageSize(), paramMap);
	 }*/

	/***
	 * 功能描述:  获取派工单编号最大值<br>
	 * @return
	 * Create author:yandawei <br>
	 * Create on:2008-12-17  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public String getMaxDispatchFormCodeFromDB() {
		Query query = super.createSQLQuery("SELECT DISPATCH_NO FROM CE_DISPATCH_FORM a where a.SYSDATE=(select max(`SYSDATE`) from CE_DISPATCH_FORM where DATE_FORMAT(`SYSDATE`,'%Y-%m-%d')=CURRENT_DATE )");
		Object obj = query.uniqueResult();
		if(BlankUtil.isBlank(obj))
			return null;
		String count = (String) obj;
		return count;
	}
	
	/**
	 * 功能描述:查询指定派工单的费用清单金额合计  <br>
	 * @param dispatchFormId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-12-29  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public BigDecimal getServItemTotalFee(Long dispatchFormId){
		String sql = "SELECT SUM(CCSI.FEE) FROM CE_CUST_SERV_ITEM CCSI WHERE CCSI.DISPATCH_FORM_ID=? ";
		Query query = super.createSQLQuery(sql, dispatchFormId);
		Object obj = query.uniqueResult();
		BigDecimal totalFee = obj==null?null:(BigDecimal)obj;
		return totalFee;
	}

	/**
	    * 功能描述: 根据部门ID查询派工单分页列表，searchVO中封装其余搜索条件 <br>
	    * <ul>
	    * <li>[0]：派工单id</li>
	    * <li>[1]：派工单编号</li>
	    * <li>[2]：房号</li>
	    * <li>[3]：服务内容</li>
	    * <li>[4]：联系人</li>
	    * <li>[5]：部门ID</li>
	    * <li>[6]：紧急程度名称</li>
	    * <li>[7]：开工时间</li>
	    * <li>[8]：完工时间</li>
	    * <li>[9]：状态</li>
	    * <li>[10]：创建人ID</li>
	    * <li>[11]：任务id</li>
	    * <li>[12]：事件ID</li>
	    * <li>[13]：房屋id</li>
	    * <li>[14]：详细地址</li>
	    * </ul>
	    * @param searchVO
	    * @return
	    *
	    * Create author:付湘辉 <br>
	    * Create on:2008-12-19  <br>
	    * Edit author:  <br>
	    * Edit on:    <br>
	    * Why:     <br>
	    */
	   public Page queryDispatchFormDeptByPage(DispatchFormSearchVO searchVO){
		   SqlConfig sqlConfig = new SqlConfig(CUST_EVENT_DISPATCHFORM_SQL);
		   StringBuilder sb = new StringBuilder(sqlConfig.getSql(KEY_QUERY_DISPATCHFORM_BYDEPT));
		   sb.append(generateWhereAndOrderSentence(searchVO));
		   return super.pagedSqlQueryByPageNo(sb.toString(), searchVO.getPageNo(), searchVO.getPageSize(),new Object[]{ searchVO.getDeptId()});
	   }


   /**
    * 功能描述: 根据事件ID查询派工单分页列表，searchVO中封装其余搜索条件 <br>
    * <ul>
    * <li>[0]：派工单id</li>
    * <li>[1]：派工单编号</li>
    * <li>[2]：房号</li>
    * <li>[3]：服务内容</li>
    * <li>[4]：联系人</li>
    * <li>[5]：部门ID</li>
    * <li>[6]：紧急程度名称</li>
    * <li>[7]：开工时间</li>
    * <li>[8]：完工时间</li>
    * <li>[9]：状态</li>
    * <li>[10]：创建人ID</li>
    * <li>[11]：任务id</li>
    * <li>[12]：事件ID</li>
    * <li>[13]：房屋id</li>
    * <li>[14]：详细地址</li>
    * </ul>
    * @param searchVO
    * @return
    *
    * Create author:付湘辉 <br>
    * Create on:2008-12-19  <br>
    * Edit author:  <br>
    * Edit on:    <br>
    * Why:     <br>
    */
   public Page queryDispatchFormEventByPage(DispatchFormSearchVO searchVO){
	   SqlConfig sqlConfig = new SqlConfig(CUST_EVENT_DISPATCHFORM_SQL);
	   StringBuilder sb = new StringBuilder(sqlConfig.getSql(KEY_QUERY_DISPATCHFORM_BYEVENT));

	   sb.append(generateWhereAndOrderSentence(searchVO));

	   return super.pagedSqlQueryByPageNo(sb.toString(), searchVO.getPageNo(), searchVO.getPageSize(),new Object[]{ searchVO.getCustEventId()});
   }

   /**
    * 功能描述:查询反馈分页列表，在搜索VO中封装派工单ID  <br>
    * @param searchVO 搜索VO，
    * @return
    *
    * Create author:付湘辉 <br>
    * Create on:2008-12-22  <br>
    * Edit author:  <br>
    * Edit on:    <br>
    * Why:     <br>
    */
   public Page queryFeedbackRecordByPage(FeedbackSearchVO searchVO) {
	   Long dispatchFormId = searchVO.getDispatchFormId();
	   StringBuilder hql = new StringBuilder();
	   hql.append("from FeedbackRecord fr where fr.dispatchForm.id=? ");
	   hql.append("order by ");
	   if (!BlankUtil.isBlank(searchVO.getSortStr())) {
		   hql.append("fr.").append(searchVO.getOrderValue())
		   	    .append(" ")
	       	    .append(searchVO.getOrderKey());
	   }else{
		   hql.append(" fr.recordTime desc ");
	   }

	   return super.pagedQueryByPageNo(hql.toString(), searchVO.getPageNo(), searchVO.getPageSize(), new Object[]{ dispatchFormId});
   }

   /**
	 * 功能描述:根据派工单ID查询服务费用项目分页列表  <br>
	 * @param dispatchFormId
	 * @return
	 *
	 * Create author:付湘辉 <br>
	 * Create on:2008-12-17  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
  public Page queryServItemByPage(ServItemSearchVO searchVO) {
	   	String hql = "from AbstractServiceItem itm where itm.dispatchFormDO.id=? ";
		return super.pagedQueryByPageNo(hql, searchVO.getPageNo(), searchVO.getPageSize(),new Object[]{searchVO.getDispatchFormId()});
  }

   /**
 * 功能描述:根据派工单ID查询所有服务费用项目   <br>
 * @param dispatchFormId
 * @return
 *
 * Create author:付湘辉 <br>
 * Create on:2008-12-17  <br>
 * Edit author:  <br>
 * Edit on:    <br>
 * Why:     <br>
 */
   public List<AbstractServiceItem> queryServItemListByDFormId(Long dispatchFormId) {
	   	String hql = "from AbstractServiceItem item where item.dispatchFormDO.id=? ";
		Query query = super.createQuery(hql, dispatchFormId);
		List<AbstractServiceItem> list = query.list();
		return list;
   }

   /**
    * 功能描述:生成查询派工单分页列表的条件和排序子句  <br>
    * @param searchVO
    * @return
    *
    * Create author:付湘辉 <br>
    * Create on:2008-12-19  <br>
    * Edit author:  <br>
    * Edit on:    <br>
    * Why:     <br>
    */
   private String generateWhereAndOrderSentence(DispatchFormSearchVO searchVO) {
	   StringBuilder sb = new StringBuilder();
	   if (!BlankUtil.isBlank(searchVO.getDispatchNo())) {
		   sb.append(" AND CDF.DISPATCH_NO LIKE '%")
		   	   .append(StringAnalysiUtil.toTropicalString(searchVO.getDispatchNo(), StringAnalysiUtil.TROPE_TEMPLATE_ALL)).append("%' ");
	   }
	   if (!BlankUtil.isBlank( searchVO.getHouseNumber())) {
		   sb.append(" AND CDF.HOUSE_NUMBER LIKE '%")
		   	   .append(StringAnalysiUtil.toTropicalString(searchVO.getHouseNumber(), StringAnalysiUtil.TROPE_TEMPLATE_ALL)).append("%' ");
	   }
	   if (!BlankUtil.isBlank(searchVO.getServiceContent())) {
		   sb.append(" AND CDF.SERVICE_CONTENT LIKE '%")
		   	.append(StringAnalysiUtil.toTropicalString(searchVO.getServiceContent(), StringAnalysiUtil.TROPE_TEMPLATE_ALL)).append("%' ");
	   }

	   sb.append("ORDER BY ");
	   if (!BlankUtil.isBlank(searchVO.getSortStr())) {
		   if ("EMERGENCYDEGREENAME".equalsIgnoreCase(searchVO.getOrderValue())) {
			   sb.append("CED.SEQUENCE");
		   }else{
			   sb.append(searchVO.getOrderValue().toUpperCase());
		   }
		   
		   sb.append(" ").append(searchVO.getOrderKey());
	   }else{
		   sb.append("CDF.DISPATCH_NO DESC");
	   }

	   return sb.toString();
   }
}
